| Document status | 35 - Reviewed |
|---|
Server Validation Script for PSQL VM
This document describes the PostgreSQL health validation script used to verify that the database server is functioning correctly after deployments, maintenance operations, or as part of routine health checks.
Overview
The validation script connects to the PostgreSQL VM via SSH and performs a comprehensive series of checks to ensure the database is operational, accessible, and performing within expected parameters. The script runs automatically as part of CI/CD pipelines on a self-hosted agent with direct network access to the database VM.
How to Run
To execute the server validation:
- Open the pipeline - Navigate to Server Validation - PSQL in Azure DevOps
- Start the pipeline - Click Run Pipeline in the top right corner
- Select environment - Choose which environment to validate from the dropdown
- Monitor the results - Review the pipeline output to see each validation check and its pass/fail status
Validation Checks
The script performs the following health checks in sequence:
Check 1: PostgreSQL Service Status
Verifies that the PostgreSQL service is running on the VM.
| Aspect | Details |
|---|---|
| Command | systemctl is-active postgresql |
| Pass Condition | Service is active and running |
| Failure Impact | Critical - indicates the database server is down |
Check 2: Database Exists
Confirms that the application database exists in PostgreSQL.
| Aspect | Details |
|---|---|
| Command | Query pg_database system catalog |
| Pass Condition | Database is found in the catalog |
| Failure Impact | Critical - application cannot function without the database |
Check 3: Accepting Connections
Verifies that PostgreSQL is ready to accept incoming connections.
| Aspect | Details |
|---|---|
| Command | pg_isready utility |
| Pass Condition | PostgreSQL reports ready status |
| Failure Impact | Critical - applications cannot connect to the database |
Check 4: Active Connections
Counts the number of active database connections and total connections.
| Aspect | Details |
|---|---|
| Query | pg_stat_activity system view |
| Metrics Reported | Active queries, total connections |
| Purpose | Informational - helps identify connection pool issues |
Check 5: Application Connections
Verifies that application services have established connections to the database.
| Aspect | Details |
|---|---|
| Query | Count connections from application database users |
| Pass Condition | Connections meet minimum expected threshold |
| Failure Impact | Fail - applications are not connected to the database |
This check examines connections from application-specific database users (such as writer and reader accounts) to ensure that the application layer is properly connected to the database.
Check 6: Query Response Test
Executes a simple query to verify the database responds to commands.
| Aspect | Details |
|---|---|
| Query | SELECT 1 |
| Metrics Reported | Query execution time in milliseconds |
| Pass Condition | Query returns expected result |
| Failure Impact | Critical - indicates database is unresponsive |
Check 7: Connection Details
Provides detailed breakdown of database connections for diagnostic purposes.
| Information | Description |
|---|---|
| Connections by User | Shows connection count per database user with active/idle breakdown |
| Connections by Client Address | Shows which IP addresses have connections to the database |
This information is useful for identifying:
- Which services are connected
- Connection pool behavior
- Potential connection leaks
Check 8: Disk Space
Monitors disk usage on the PostgreSQL VM to prevent space-related issues.
| Aspect | Details |
|---|---|
| Command | df -h on data volumes |
| Threshold | 85% usage |
| Failure Impact | Fail - database may become read-only or crash if disk fills up |
The check examines all mounted data volumes and reports:
- Filesystem name
- Total size
- Used space
- Available space
- Usage percentage
- Mount point
Check 9: WAL-G Backup Status (Optional)
When enabled, validates that the backup system is properly configured and functioning.
| Aspect | Details |
|---|---|
| Backup Tool | WAL-G |
| Checks Performed | Executable exists, configuration present, cron job scheduled, recent backups exist |
| Pass Condition | Latest backup within 48 hours |
This check verifies:
- WAL-G executable is installed at expected location
- Configuration file exists
- Backup cron job is scheduled for the postgres user
- Recent backups can be listed
- Latest backup is within acceptable age threshold
Note: Backup checks are disabled by default and must be explicitly enabled for environments where backups are configured.
Check 10: Passwordless Sudo
Verifies that the VM admin user can run sudo su without being prompted for a password.
| Aspect | Details |
|---|---|
| Command | sudo -n true |
| Pass Condition | sudo executes without password prompt |
| Failure Impact | Fail - password rotation and other automated operations will break |
The VMAccessForLinux extension can create /etc/sudoers.d/waagent during password rotation, which overrides the NOPASSWD rule from cloud-init. The password rotation script automatically removes this file after rotating passwords to prevent this issue.
Validation Summary
After all checks complete, the script outputs a summary with the status of each validation:
| Result Variable | Description |
|---|---|
SERVICE_RUNNING | PostgreSQL service is active |
DATABASE_EXISTS | Application database exists |
ACCEPTING_CONNECTIONS | Database is accepting connections |
TOTAL_CONNECTIONS | Total number of database connections |
APP_CONNECTIONS | Number of application service connections |
QUERY_RESPONSE | Database responds to queries |
DISK_OK | All disks below 85% threshold |
SUDO_NOPASSWD | Passwordless sudo is working |
BACKUP_CONFIGURED | Backup system is operational (when enabled) |
VALIDATION_PASSED | Overall validation result |
Exit Codes
| Code | Meaning |
|---|---|
0 | All validations passed - database is healthy |
1 | One or more validations failed - review output for details |
Troubleshooting
Common Issues
| Symptom | Possible Cause | Resolution |
|---|---|---|
| SSH connection fails | Incorrect credentials, network issue | Check Key Vault for correct password, verify agent network access |
| Service not running | VM was restarted, service crashed | Check VM status, review PostgreSQL logs |
| No application connections | Container Apps not running, connection string issues | Verify Container Apps are healthy, check connection string secrets |
| Disk space critical | Log files, WAL accumulation, data growth | Review and clean logs, verify backup archiving |
| Query timeout | Database overloaded, long-running queries | Check pg_stat_activity for blocking queries |